package com.fpost.dao.impl;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.hibernate.Criteria;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.hibernate.criterion.DetachedCriteria;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.ProjectionList;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Property;
import org.hibernate.criterion.Restrictions;

import com.fpost.dao.GenericDAO;
import com.fpost.domain.Article;
import com.fpost.domain.User;

public class ArticleDAO extends GenericDAO<Article> {
		
	@Override
	public Long save(Article article) throws HibernateException {
		Long articleId = null;
		Session session = getSessionFactory().openSession();
		Transaction tx = null;
		try {
			tx = session.beginTransaction();
			articleId = (Long) session.save(article);
			tx.commit();
		} catch (HibernateException e) {
			tx.rollback();
			throw e;
		} finally {
			session.close();
		}
		return articleId;
	}
	

	
	@Deprecated
	public void saveArticles(List<Article> articles) throws HibernateException {
		Session session = getSessionFactory().openSession();
		Long articleId = null;
		Transaction tx = null;
		long counter = 0;
		try {
			tx = session.beginTransaction();
			for (Article article : articles) {
				articleId = (Long) session.save(article);
				counter++;
				if(counter == 100) {
					session.flush();
					counter = 0;
				}				
			}			
			tx.commit();
		} catch (HibernateException e) {
			tx.rollback();
			throw e;
		} finally {
			session.close();
		}
	}
	
	@Override
	public void saveOrUpdate(Article article) throws HibernateException {
		Session session = getSessionFactory().openSession();
		Transaction tx = null;
		try {
			tx = session.beginTransaction();
			session.saveOrUpdate(article);
			tx.commit();
		} catch (HibernateException e) {
			tx.rollback();
			throw e;
		} finally {
			session.close();
		}
	}

	@Override
	public void delete(long id) throws HibernateException {
		Session session = getSessionFactory().openSession();
		Transaction tx = null;
		try {
			tx = session.beginTransaction();
			session.delete(get(id));
			tx.commit();
		} catch (HibernateException e) {
			tx.rollback();
			throw e;
		} finally {
			session.close();
		}
	}

	@Override
	public void delete(Article article) throws HibernateException {
		Session session = getSessionFactory().openSession();
		Transaction tx = null;
		try {
			tx = session.beginTransaction();
			session.delete(article);
			tx.commit();
		} catch (HibernateException e) {
			tx.rollback();
			throw e;
		} finally {
			session.close();
		}
	}

	@Override
	public Article get(long id) throws HibernateException {
		Article article = null;
		Session session = getSessionFactory().openSession();		
		Transaction tx = null;
		try {
			tx = session.beginTransaction();
			article = (Article) session.get(Article.class, id);
			tx.commit();
		} catch (HibernateException e) {
			tx.rollback();
			throw e;
		} finally {
			session.close();
		}
		return article;
	}

	@Deprecated
	@SuppressWarnings("unchecked")
	public List<Article> getCurrentArticlesByUserId_HQL(long userId) throws HibernateException {
		String queryStr = "from Article where userId = :userId and deleted = false";
		List<Article> articles = null;
		Session session = getSessionFactory().openSession();
		Transaction tx = null;
		try {
			tx =session.beginTransaction();
			Query query = session.createQuery(queryStr);
			query.setParameter("userId", userId);
			articles = (List<Article>)query.list();
			tx.commit();
		} catch (HibernateException e) {
			tx.rollback();
			throw e;
		} finally {
			session.close();
		}
		return articles;
	}
	
//	@SuppressWarnings("unchecked")
//	public Article getByArticleId(long articleId) throws HibernateException {
//		Session session = getSessionFactory().openSession();
//		
//		Article article = (Article)session.createCriteria(Article.class)
//					.add(Restrictions.idEq(new Long(articleId))).uniqueResult();
//
//		return article;
//	}
	
	public Article getArticleById(long articleId) throws HibernateException {
		Session session = getSessionFactory().openSession();		
		Article article = null;
		try {
			article = (Article)session.createCriteria(Article.class)
				.add(Restrictions.eq("articleId", articleId)).uniqueResult();
		} catch (HibernateException e) {
			throw e;
		} finally {
			session.close();
		}		
		return article;
	}
	
	public Article getArticleByIdIncrementHitCount(long articleId, String ipAddress) throws HibernateException {
		Session session = getSessionFactory().openSession();		
		Transaction tx = null;
		Article article = null;
		try {
			DetachedCriteria query = DetachedCriteria.forClass(Article.class)
			    .add( Property.forName("articleId").eq(new Long(articleId)))
				.add( Property.forName("deleted").eq(new Boolean(false)));
			
			tx = session.beginTransaction();
			article = (Article)query.getExecutableCriteria(session).uniqueResult();
 
			if (new IpDAO().isNewIpAddress(articleId, ipAddress)) {
				article.incrementHitCount();
			}
			session.saveOrUpdate(article);
			article = (Article)query.getExecutableCriteria(session).uniqueResult();
			tx.commit();
		} catch (HibernateException e) {
			tx.rollback();
			throw e;
		} finally {
			session.close();
		}		
		return article;
	}
	
	public long getUserIdByArticleId(long articleId) throws HibernateException {
		String queryStr = "select user.userId from Article where articleId = :articleId";
		long userId = 0L;
		Session session = getSessionFactory().openSession();
		Transaction tx = null;
		try {
			tx =session.beginTransaction();
			Query query = session.createQuery(queryStr);
			query.setParameter("articleId", articleId);
			Long id = (Long)query.uniqueResult();
			if (id != null) {
				userId = id.longValue();
			}
			tx.commit();
		} catch (HibernateException e) {
			tx.rollback();
			throw e;
		} catch (Exception e) {
			throw e;
		} finally {
			session.close();
		}
		return userId;
	}

	@SuppressWarnings("unchecked")
	public List<Article> getCurrentArticlesByUser(User user) throws HibernateException {
		Session session = getSessionFactory().openSession();
		List<Article> articles = (List<Article>) session.createCriteria(Article.class)
				.add(Restrictions.eq("user", user))
				.add(Restrictions.eq("deleted", false))
				.list();
		return articles;
	}
	@SuppressWarnings("unchecked")
	public List<Article> getDeletedArticlesByUser(User user) throws HibernateException {
		Session session = getSessionFactory().openSession();
		List<Article> articles = (List<Article>) session.createCriteria(Article.class)
				.add(Restrictions.eq("user", user))
				.add(Restrictions.eq("deleted", true))
				.list();
		return articles;
	}
	@SuppressWarnings("unchecked")
	public List<Article> getAllArticlesByUser(User user) throws HibernateException {
		Session session = getSessionFactory().openSession();
		List<Article> articles = (List<Article>) session.createCriteria(Article.class)
				.add(Restrictions.eq("user", user)).list();
		return articles;
	}
	
	@SuppressWarnings("unchecked")
	public long countCurrentArticlesByUsername_HQL(String username) throws HibernateException {
		String queryStr = 
				"select count(a) from Article a, User u " +
				"where a.user.userId = u.userId " +
				"and u.username = :username " +  
				"and a.deleted = false";
		long count = 0;
		Session session = getSessionFactory().openSession();
		Transaction tx = null;
		try {
			tx = session.beginTransaction();
			Query query = session.createQuery(queryStr);
			query.setParameter("username", username);
			count = (Long)query.uniqueResult();
			tx.commit();			
		} catch (HibernateException e) {
			tx.rollback();
			throw e;
		} finally {
			session.close();
		}
		return count;
	}
	
	@SuppressWarnings("unchecked")
	public List<Article> getCurrentArticlesByUsername(String username) throws HibernateException {
		Session session = getSessionFactory().openSession();
		
		User user = (User)session.createCriteria(User.class)
				.add(Restrictions.eq("username", username)).uniqueResult();
		
		List<Article> articles = (List<Article>) session.createCriteria(Article.class)
				.add(Restrictions.eq("user.userId", user.getUserId()))
				.add(Restrictions.eq("deleted", false))
				.list();
		return articles;
	}
	
	@SuppressWarnings("unchecked")
	public List<Article> getDeletedArticlesByUsername(String username) throws HibernateException {
		Session session = getSessionFactory().openSession();
		List<Article> articles = (List<Article>) session.createCriteria(Article.class)
				.add(Restrictions.eq("username", username))
				.add(Restrictions.eq("deleted", true))
				.list();
		return articles;
	}
	
	@SuppressWarnings("unchecked")
	public List<Article> getAllArticlesByUsername(String username) throws HibernateException {
		Session session = getSessionFactory().openSession();
		List<Article> articles = (List<Article>) session.createCriteria(Article.class)
				.add(Restrictions.eq("username", username)).list();
		return articles;
	}
	
	public boolean isCurrentArticleExists(long articleId) {
		String queryStr = 
				"select count(a) from Article a " +
				"where a.articleId = :articleId " +
				"and a.deleted = false";
		long count = 0;
		Session session = getSessionFactory().openSession();
		Transaction tx = null;
		try {
			tx = session.beginTransaction();
			Query query = session.createQuery(queryStr);
			query.setParameter("articleId", articleId);
			count = (Long)query.uniqueResult();
			tx.commit();			
		} catch (HibernateException e) {
			tx.rollback();
			throw e;
		} finally {
			session.close();
		}
		return (count == 1 ? true : false);
	}
	
	@SuppressWarnings("unchecked")
	public long countCurrentArticlesByUserId_HQL(long userId) throws HibernateException {
		String queryStr = 
				"select count(a) from Article a " +
				"where a.user.userId = :userId " +
				"and a.deleted = false";
		long count = 0;
		Session session = getSessionFactory().openSession();
		Transaction tx = null;
		try {
			tx = session.beginTransaction();
			Query query = session.createQuery(queryStr);
			query.setParameter("userId", userId);
			count = (Long)query.uniqueResult();
			tx.commit();			
		} catch (HibernateException e) {
			tx.rollback();
			throw e;
		} finally {
			session.close();
		}
		return count;
	}	
	
	@SuppressWarnings("unchecked")
	public List<Article> getCurrentArticlesByUserId(long userId) throws HibernateException {
		Session session = getSessionFactory().openSession();
		List<Article> articles = (List<Article>) session.createCriteria(Article.class)
				.addOrder(Order.desc("createdOn"))
				.add(Restrictions.eq("user.userId", userId))
				.add(Restrictions.eq("deleted", false))
				.list();
		return articles;
	}
	
	@SuppressWarnings("unchecked")
	public List<Article> getDeletedArticlesByUserId(long userId) {
		Session session = getSessionFactory().openSession();
		List<Article> articles = (List<Article>) session.createCriteria(Article.class)
				.add(Restrictions.eq("user.userId", userId))
				.add(Restrictions.eq("deleted", true))
				.list();
		return articles;		
	}
	
	@SuppressWarnings("unchecked")
	public List<Article> getAllArticlesByUserId(long userId) {
		Session session = getSessionFactory().openSession();
		List<Article> articles = (List<Article>) session.createCriteria(Article.class)
				.add(Restrictions.eq("user.userId", userId))
				.list();
		return articles;		
	}
	
	@Deprecated
	@SuppressWarnings("unchecked")
	public List<Article> getArticlesByInputKeyCrit(String inputKey) throws HibernateException {
		Session session = getSessionFactory().openSession();
 
		Criteria c = session.createCriteria(Article.class);
		// only consent UI requires comments to be populated, 
		//removed lazy load from hibernate config to reduce traffic on
		// consent checks, added comments population here
//		c.createCriteria(CONSENT_DIRECTIVE_COMMENTS, JoinFragment.LEFT_OUTER_JOIN)
//				.setFetchMode(CONSENT_DIRECTIVE_COMMENTS, FetchMode.SELECT);
//
//		        c.add(Expression.eq(CONSENT_DIRECTIVE_ID, consentDirectiveId))
//		        if (true) {
//		        	 c.add(Expression.in(FIELD_CDTYPE_CD_TYPE_ID, consentDirectiveTypeIds));
//		        }
//		        if (true) {
//		        	 c.add(Expression.disjunction().add(Expression.ge(FIELD_EFF_TO,
//		        			 eventToCompare)).add(Expression.isNull(FIELD_EFF_TO)));
//		        }
//				.add(Restrictions.like("body", "%" + inputKey + "%"))
//				// .add( Restrictions.like("keywords", "%" + inputKey + "%"));
		
//  criterion = Restrictions.ilike("name", searchValue, MatchMode.ANYWHERE);		
		
		List<Article> articles = c.list();
		
		return articles;
	}
	
	@SuppressWarnings("unchecked")
	public List<Article> getCurrentArticlesByInputAndType_HQL(String input, long articleTypeId) throws HibernateException {

		//String[] keys = input.split(" ");		
		StringBuilder hqlQuery = new StringBuilder();		
		hqlQuery.append("select distinct(a) from Article a, ArticleVersion av ");	
		hqlQuery.append("where (av.title like :input ");
		hqlQuery.append("or av.summary like :input ");		
		hqlQuery.append("or av.body like :input ");
		hqlQuery.append("or av.keywords like :input) ");
		if (articleTypeId != 0) {
			hqlQuery.append("and av.articleTypeId = :articleTypeId ");
		}
		hqlQuery.append("and a.articleId = av.article.articleId "); 
		hqlQuery.append("and a.deleted = false");
		
		List<Article> articles = null;
		Session session = getSessionFactory().openSession();
		Transaction tx = null;
		try {
			tx = session.beginTransaction();
			Query query = session.createQuery(hqlQuery.toString());
			query.setParameter("input", '%' + input + '%');
			if (articleTypeId != 0) {query.setParameter("articleTypeId", articleTypeId);}
			articles = (List<Article>)query.list();
			tx.commit();			
		} catch (HibernateException e) {
			tx.rollback();
			throw e;
		} finally {
			session.close();
		}
		return articles;
	}
	
	@SuppressWarnings("unchecked")
	public long countCurrentArticlesByInputAndType_HQL(String input, long articleTypeId) throws HibernateException {
		
		//String[] keys = input.split(" ");
		StringBuilder hqlQuery = new StringBuilder();
		hqlQuery.append("select count(distinct a) from Article a, ArticleVersion av ");
		hqlQuery.append("where (av.title like :input ");
		hqlQuery.append("or av.summary like :input ");		
		hqlQuery.append("or av.body like :input ");
		hqlQuery.append("or av.keywords like :input) ");
		if (articleTypeId != 0) {
			hqlQuery.append("and av.articleTypeId = :articleTypeId ");
		}
		hqlQuery.append("and a.articleId = av.article.articleId "); 
		hqlQuery.append("and a.deleted = false");
		
		long count = 0;
		Session session = getSessionFactory().openSession();
		Transaction tx = null;
		try {
			tx = session.beginTransaction();
			Query query = session.createQuery(hqlQuery.toString());
			query.setParameter("input", '%' + input + '%');
			if (articleTypeId != 0) {query.setParameter("articleTypeId", articleTypeId);}
			count = (Long)query.uniqueResult();
			tx.commit();			
		} catch (HibernateException e) {
			tx.rollback();
			throw e;
		} finally {
			session.close();
		}
		return count;
	}
	
	public List<Article> getCurrentArticlesByArticleIds(List<Long> articleIds) throws HibernateException {
		Session session = getSessionFactory().openSession();
		List<Article> articles;
		try {
			articles = (List<Article>) session.createCriteria(Article.class)
				.add(Restrictions.in("articleId", articleIds))
				.add(Restrictions.eq("deleted", false))
				.list();
		} catch (HibernateException e) {
			e.printStackTrace();
			throw e;
		} finally {
			session.close();
		}
		return articles;
	}
	
	public List<Long> getCurrentArticleIds() throws HibernateException {
		Session session = getSessionFactory().openSession();
		List<Long> list = null;
		try {
			Criteria crit = session.createCriteria(Article.class)
					.add(Restrictions.eq("deleted", false));
			ProjectionList projectionList = Projections.projectionList();
			projectionList.add(Projections.property("articleId"));
			crit.setProjection(projectionList);
			list = (List<Long>)crit.list();
		} catch (HibernateException e) {
			e.printStackTrace();
			throw e;
		} finally {
			session.close();
		}
		return list;
	}
	
	public List<Long> getAllArticleIds() throws HibernateException {
		Session session = getSessionFactory().openSession();
		List<Long> list = null;
		try {
			Criteria crit = session.createCriteria(Article.class);					
			ProjectionList projectionList = Projections.projectionList();
			projectionList.add(Projections.property("articleId"));
			crit.setProjection(projectionList);
			list = (List<Long>)crit.list();
		} catch (HibernateException e) {
			e.printStackTrace();
			throw e;
		} finally {
			session.close();
		}
		return list;
	}
	
	public List<Long> getCurrentArticleIdsByInDictionaryFlag(boolean inDictionary) throws HibernateException {
		Session session = getSessionFactory().openSession();
		List<Long> list = null;
		try {	
			Criteria crit = session.createCriteria(Article.class)
					.add(Restrictions.eq("deleted", false))
					.add(Restrictions.eq("inDictionary", inDictionary));
			ProjectionList projectionList = Projections.projectionList();
			projectionList.add(Projections.property("articleId"));
			crit.setProjection(projectionList);
			list = (List<Long>)crit.list();
		} catch (HibernateException e) {
			e.printStackTrace();
			throw e;
		} finally {
			session.close();
		}
		return list;
	}
	
	public List<Long> getCurrentArticleIdsByUsername(String username) throws HibernateException {
		Session session = getSessionFactory().openSession();
		List<Long> list = null;
		try {
			Criteria crit = session.createCriteria(Article.class)
					.createAlias("user", "user")
					.add(Restrictions.eq("user.username", username))
					.add(Restrictions.eq("deleted", false));		
			ProjectionList projectionList = Projections.projectionList();
			projectionList.add(Projections.property("articleId"));
			crit.setProjection(projectionList);		
			list = (List<Long>)crit.list();
		} catch (HibernateException e) {
			e.printStackTrace();
			throw e;
		} finally {
			session.close();
		}
		return list;
	}
	
	public List<Long> selectArticleIdsByArticleTypeId_HQL(List<Long> articleIds, long articleTypeId) {
		
//		Session session = getSessionFactory().openSession();
//		Criteria crit = session.createCriteria(Article.class)
//				
//		ProjectionList projectionList = Projections.projectionList();
//		projectionList.add(Projections.property("articleId"));
//		crit.setProjection(projectionList);
//		List<Long> list = (List<Long>)crit.list();
//		return list;
		
		if(articleIds.isEmpty()) {
			return new ArrayList<Long>();
		}
		if(articleTypeId == 0) {
			return articleIds;
		}
		
		StringBuilder sb = new StringBuilder();
		for (Long id : articleIds) {
			sb.append(id).append(",");
		}
		String ids = sb.toString();
		ids = ids.substring(0, ids.length()-1);
		
		StringBuilder hqlQuery = new StringBuilder();
		hqlQuery.append("select a.articleId from Article a, ArticleVersion av ");
		hqlQuery.append("where a.articleId = av.article.articleId ");
		hqlQuery.append("and av.article.articleId in (").append(ids + ")");
		if (articleTypeId != 0) {
			hqlQuery.append("and av.articleTypeId = :articleTypeId ");
		}
		
		List<Long> selectedIds = null;
		Session session = getSessionFactory().openSession();
		Transaction tx = null;
		try {
			tx = session.beginTransaction();
			Query query = session.createQuery(hqlQuery.toString());
			if (articleTypeId != 0) {query.setParameter("articleTypeId", articleTypeId);}
			selectedIds = (List<Long>)query.list();
			tx.commit();			
		} catch (HibernateException e) {
			tx.rollback();
			throw e;
		} finally {
			session.close();
		}		
		return selectedIds;
	}
	
	@SuppressWarnings("unchecked")
	public List<Article> getCurrentArticles(int maxResults) throws HibernateException {
		Session session = getSessionFactory().openSession();
		List<Article> articles;
		try {
			articles = (List<Article>) session.createCriteria(Article.class)
					.add(Restrictions.eq("deleted", false))
					.setMaxResults(maxResults)
					.list();
		} catch (HibernateException e) {
			e.printStackTrace();
			throw e;
		}
		return articles;		
	}
	
	@SuppressWarnings("unchecked")
	public List<Article> getAllCurrentArticles() throws HibernateException {
		Session session = getSessionFactory().openSession();
		List<Article> articles;
		try {
			articles = (List<Article>) session.createCriteria(Article.class)
					.add(Restrictions.eq("deleted", false))
					.list();
		} catch (HibernateException e) {
			e.printStackTrace();
			throw e;
		}
		return articles;		
	}
	
	@SuppressWarnings("unchecked")
	public List<Article> getMostRecentCurrentArticles_HQL(int maxResult) throws HibernateException {
		StringBuilder hqlQuery = new StringBuilder();
		hqlQuery.append("select a from Article a, ArticleVersion av ");	
		hqlQuery.append("where a.articleId = av.articleId ");
		hqlQuery.append("group by a order by s ");		
		
		List<Article> articles = null;
		Session session = getSessionFactory().openSession();
		Transaction tx = null;
		try {
			tx =session.beginTransaction();
			Query query = session.createQuery(hqlQuery.toString());
			articles = (List<Article>)query.list();
			tx.commit();
		} catch (HibernateException e) {
			tx.rollback();
			throw e;
		} finally {
			session.close();
		}
		return articles;
	}
	
	@SuppressWarnings("unchecked")
	public List<Article> getMostRecentCurrentArticles(int maxResult) throws HibernateException {
		Session session = getSessionFactory().openSession();
		List<Article> articles;		
		try {
			articles = (List<Article>) session.createCriteria(Article.class)
					.addOrder(Order.desc("createdOn"))
					.add(Restrictions.eq("deleted", false))
					.setMaxResults(maxResult)
					.list();
		} catch (HibernateException e) {
			e.printStackTrace();
			throw e;
		} finally {
			session.close();
		}
		return articles;
	}
	
	@SuppressWarnings("unchecked")
	public List<Article> getMostRecentCurrentArticles(Date from, Date to, int maxResult) throws HibernateException {
		Session session = getSessionFactory().openSession();
		List<Article> articles;		
		try {
			articles = (List<Article>) session.createCriteria(Article.class)
					.addOrder(Order.desc("createdOn"))
					.add(Restrictions.between("createdOn", from, to))
					.add(Restrictions.eq("deleted", false))
					.setMaxResults(maxResult)
					.list();
		} catch (HibernateException e) {
			e.printStackTrace();
			throw e;
		} finally {
			session.close();
		}
		return articles;
	}
	
	@SuppressWarnings("unchecked")
	public List<Article> getMostPopularCurrentArticles(int maxResult) throws HibernateException {
		Session session = getSessionFactory().openSession();
		List<Article> articles;		
		try {
			articles = (List<Article>) session.createCriteria(Article.class)
					.addOrder(Order.desc("hitCount"))
					.add(Restrictions.eq("deleted", false))
					.setMaxResults(maxResult)
					.list();
		} catch (HibernateException e) {
			e.printStackTrace();
			throw e;
		} finally {
			session.close();
		}
		return articles;
	}
	
	@SuppressWarnings("unchecked")
	public List<Article> getMostPopularCurrentArticles(Date from, Date to, int maxResult) throws HibernateException {
		Session session = getSessionFactory().openSession();
		List<Article> articles;		
		try {
			articles = (List<Article>) session.createCriteria(Article.class)
					.addOrder(Order.desc("hitCount"))
					.add(Restrictions.between("createdOn", from, to))
					.add(Restrictions.eq("deleted", false))
					.setMaxResults(maxResult)
					.list();
		} catch (HibernateException e) {
			e.printStackTrace();
			throw e;
		} finally {
			session.close();
		}
		return articles;
	}	
	
	@SuppressWarnings("unchecked")
	public List<Object[]> getHighestRatingCurrentArticles_SQL(java.sql.Date from, java.sql.Date to, int maxResultd) throws HibernateException {
		
		StringBuilder sqlQuery = new StringBuilder();
		sqlQuery.append("select a.article_id, avg(ar.rating) av, count(ar.rating) c ");	
		sqlQuery.append("from article a, article_rating ar ");
		sqlQuery.append("where a.article_id = ar.article_id ");
		sqlQuery.append("and a.created_on between ? and ? ");
		sqlQuery.append("and a.deleted = 0 ");
		sqlQuery.append("group by a.article_id, a.created_on ");
		sqlQuery.append("order by av desc, c desc, a.created_on desc ");
		
		List<Object[]> articles = null;
		Session session = getSessionFactory().openSession();
		Transaction tx = null;
		try {
			tx =session.beginTransaction();
			SQLQuery query = session.createSQLQuery(sqlQuery.toString());
			query.setParameter(0, from);
			query.setParameter(1, to);
			articles = (List<Object[]>)query.list();
			tx.commit();
		} catch (HibernateException e) {
			tx.rollback();
			throw e;
		} finally {
			session.close();
		}
		return articles;
	}
	
//	select a.article_id, sum(ar.rating) s from article a, article_rating ar 
//	where a.article_id = ar.article_id
//	group by a.article_id
//	order by s desc;
	
	@SuppressWarnings("unchecked")
	public List<Article> getCurrentArticlesWithHitCountMoreThan(long hitCount) throws HibernateException {
		Session session = getSessionFactory().openSession();
		List<Article> articles = (List<Article>) session.createCriteria(Article.class)
				.add(Restrictions.gt("hitCount", hitCount))
				.add(Restrictions.eq("deleted", false))
				//.setMaxResults(maxResult)
				.list();
		return articles;
	}
	
	@SuppressWarnings("unchecked")
	public List<Article> getCurrentArticlesWithHitCountMoreThan_HQL(long hitCount) throws HibernateException {
		String queryStr = "from Article a where a.hitCount > :hitCount and a.deleted = false";
		List<Article> articles = null;
		Session session = getSessionFactory().openSession();
		Transaction tx = null;
		try {
			tx =session.beginTransaction();
			Query query = session.createQuery(queryStr);
			query.setParameter("hitCount", hitCount);
			articles = (List<Article>)query.list();
			tx.commit();
		} catch (HibernateException e) {
			tx.rollback();
			throw e;
		} finally {
			session.close();
		}
		return articles;
	}
	
	@SuppressWarnings("unchecked")
	public List<Article> getAllDeletedArticles() throws HibernateException {
		Session session = getSessionFactory().openSession();
		List<Article> articles;
		try {
			articles = (List<Article>) session.createCriteria(Article.class)
					.add(Restrictions.eq("deleted", true))
					.list();
		} catch (HibernateException e) {
			e.printStackTrace();
			throw e;
		}
		return articles;		
	}
	
	@SuppressWarnings("unchecked")
	public List<Article> getAllArticles() throws HibernateException {
		Session session = getSessionFactory().openSession();
		List<Article> articles;
		try {
			articles = (List<Article>) session.createCriteria(Article.class).list();
		} catch (HibernateException e) {
			e.printStackTrace();
			throw e;
		}
		return articles;		
	}
	
	public void genereatetAllArticles(List<Article> articles) {
		
	}

}
